package com.kjlink.internal.workreport.jira.helper;


import org.apache.commons.lang3.tuple.Pair;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

/**
 * 描述:
 * 关于查询 sql server 数据库的jira 的查询语句 的帮助类
 *
 * @author Vic.xu
 * @since 2025-01-11 10:22
 */
public class SqlServerJiraSqlHelper {


    private static DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    /**
     * 查询jira的查询语句
     */
    private static final String QUERY_JIRA_SQL = "select a.pkey AS code, c.pkey  as pcode, a.CREATED as creadted,  " +
            " a.ASSIGNEE  as assignee, a.REPORTER  as reporter, a.SUMMARY  as  content,  " +
            " d.pname  as type, e.pname  as status, " +
            " g.vname  as versionName, g.RELEASEDATE  as releaseDate " +
            "from jiraissue a  " +
            "left join issuelink  b on a.ID  = b.DESTINATION  and b.LINKTYPE  = 10100 " +
            "left join jiraissue c on c.ID  = b.source  " +
            "left join issuetype d on a.issuetype  = d.ID  " +
            "left join issuestatus e on a.issuestatus = e.ID  " +
            "left join nodeassociation f on a.id = f.SOURCE_NODE_ID and  f.sink_node_entity='Version' and  f.association_type='IssueFixVersion'  " +
            "left join projectversion g on f.SINK_NODE_ID  = g.id " +
            "where 1=1  ";


    public static String getQueryJiraSql(){
        return QUERY_JIRA_SQL;
    }

    /**
     * 根据code查询jira的查询语句
     */
    public static Pair<String, Object[]> buildQueryJiraSqlByCODE(String code) {
        String sql = QUERY_JIRA_SQL + " and a.pkey = ?";
        Object[] params = {code};
        return Pair.of(sql, params);
    }

    /**
     * 根据创建日期查询jira的查询语句
     */
    public static Pair<String, Object[]> buildQueryJiraSqlByCreateDate(LocalDate startDate) {
        String sql = QUERY_JIRA_SQL + " and CONVERT(VARCHAR(10), a.CREATED, 120)  = ?";
        Object[] params = {DATE_FORMATTER.format(startDate)};
        return Pair.of(sql, params);
    }

    /**
     * 根据创建日期范围查询jira的查询语句
     */
    public static Pair<String, Object[]> buildQueryJiraSqlBetweenCreateDate(LocalDate startDate, LocalDate endDate) {
        String sql = QUERY_JIRA_SQL + " AND  CONVERT(VARCHAR(10), a.CREATED, 120)   BETWEEN ? AND ? ";
        Object[] params = {DATE_FORMATTER.format(startDate), DATE_FORMATTER.format(endDate)};
        return Pair.of(sql, params);
    }


}
